package com.kevin.action.builder;

import java.sql.Types;
import java.util.ArrayList;

import org.apache.commons.lang3.StringUtils;

import com.kevin.action.BuildSql;
import com.kevin.action.JdbcParams;
import com.kevin.enums.DBType;
import com.kevin.exceptions.ExceptionFactory;
import com.kevin.executors.Executor;
import com.kevin.sql.DataType;
import com.kevin.sql.SqlFormat;
import com.kevin.sql.SqlValue;
import com.kevin.table.MCellStruct;
import com.kevin.table.MCellValue;
import com.kevin.table.MDataCell;
import com.kevin.table.MDataColumn;
import com.kevin.table.MDataRow;
import com.kevin.action.JdbcType;

public class OracleBuildHandler extends BaseSqlBuildHandler {

	public OracleBuildHandler(String tableName, Executor executor) {
		super(tableName, executor, DBType.MYSQL);
	}

	@Override
	String buildSelectSql(int pageIndex, int pageSize, String where, int rowCount, String columns) {
		if (StringUtils.isEmpty(columns)) {
			columns = "*";
		}

		if (StringUtils.isEmpty(where)) {
			where = "1=1";
		}

		// 认为没有分页，查询所有数据
		if (pageSize == 0) {
			return String.format(PAGER, columns, tableName, where);
		}

		pageIndex = pageIndex == 0 ? 1 : pageIndex;

		String primaryKey = SqlFormat.keyword(struct.getColumns().getFirstPrimary().getColumnName(), DBType.ORACLE);// 主键列名。
		if (rowCount > 0) // 分页查询。按照主键进行排序
		{
			where = SqlUtils.addOrderBy(where, primaryKey);
		}

		int rowStart = (pageIndex - 1) * pageSize + 1;
		int rowEnd = rowStart + pageSize - 1;

		int index = tableName.lastIndexOf(')');
		if (index > 0) {
			tableName = tableName.substring(0, index + 1);
		}
		String onlyWhere = "where " + SqlUtils.removeOrderBy(where);
		onlyWhere = SqlFormat.whereOneEqOne(onlyWhere);
		return String.format(ROWNUMBERPAGER, SqlUtils.getOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns),
				tableName, onlyWhere, "", rowStart, rowEnd);
	}

	@Override
	void buildInsertSql(BuildSql buildSql, MDataRow row) {
		StringBuilder sqlbuilder = new StringBuilder();
		StringBuilder sqlbuilder2 = new StringBuilder();
		sqlbuilder.append("insert into " + tableName + "(");
		sqlbuilder2.append(") Values(");

		MDataCell cell = null;
		for (int i = 0; i < row.size(); i++) {
			cell = row.get(i);
			if (cell.getCellstruct().isAutoIncrement())
				continue;
			if (cell.isNull() && !cell.getCellstruct().isCanNull() && cell.getCellstruct().getDefaultValue() == null) {
				throw ExceptionFactory.Exception(tableName + " : " + cell.getColumnName() + " can not be null !");
			}
			if (cell.getState() != MCellValue.NONE) {
				sqlbuilder.append(SqlFormat.keyword(cell.getColumnName(), DBType.ORACLE) + ",");
				sqlbuilder2.append(Prefix() + ",");

				Object value = cell.getValue();
				Class<?> type = cell.getCellstruct().getValueType();
				if (type.equals(String.class) && cell.getStringValue().equals(StringUtils.EMPTY)
						&& !cell.getCellstruct().isCanNull()) {
					value = " ";
				}
				buildSql.addParam(new JdbcParams(value, JdbcType.forCode(cell.getCellstruct().getSqlType())));
			}
		}
		String sql = StringUtils.stripEnd(sqlbuilder.toString(), ",")
				+ StringUtils.stripEnd(sqlbuilder2.toString(), ",") + ")";
		buildSql.appendSql(sql);
	}

	@Override
	void buildUpdateSql(BuildSql buildSql, MDataRow row, String where) {
		if (StringUtils.isEmpty(where)) {
			where = getWhere(row.getJointPrimaryCell());
		}
		buildSql.appendSql("Update " + tableName + " set ");
		MDataCell cell = null;
		for (int i = 0; i < row.size(); i++) {
			cell = row.get(i);
			if (cell.getCellstruct().isPrimaryKey() || cell.getCellstruct().isAutoIncrement()) {
				continue;// 跳过自增或主键列。
			}

			if (cell.getState() > 1 && (cell.getCellstruct().isCanNull() || !cell.isNull())) {
				Object value = cell.getValue();
				Class<?> valueType = cell.getCellstruct().getValueType();
				if (valueType == String.class && cell.getStringValue().equals(StringUtils.EMPTY)
						&& !cell.getCellstruct().isCanNull()) {
					value = " ";// Oracle not null 字段，不允许设置空值。
				}

				buildSql.addParam(new JdbcParams(value, JdbcType.forCode(cell.getCellstruct().getSqlType())));
				buildSql.appendSql(SqlFormat.keyword(cell.getColumnName(), DBType.ORACLE) + "=" + Prefix() + ",");
			}
		}
		buildSql = buildSql.delete(buildSql.sqlLength() - 1, buildSql.sqlLength());
		buildSql.appendSql(" where " + where);
	}
	
	@Override
	public String Prefix() {
		return ":";
	}
	
	/**
	 * 根据元数据列组合where条件。
	 * 
	 * @param dbType
	 *            数据库类型
	 * @param isAnd
	 * @param cells
	 * @return
	 */
	@Override
	String getWhere(boolean isAnd, ArrayList<MDataCell> cells) {
		String where = "";
		MDataCell cell;
		for (int i = 0; i < cells.size(); i++) {
			cell = cells.get(i);
			if (i > 0) {
				where += (isAnd ? " and " : " or ");
			}
			int groupID = DataType.getGroup(cell.getCellstruct().getSqlType());
			String columnName = SqlFormat.keyword(cell.getColumnName(), DBType.ORACLE);
			switch (groupID) {
			case 1:
				where += columnName + "=" + (cell.isEmpty() ? -9999 : cell.getValue());
				break;
			case 3:
				where += columnName + "="
						+ (cell.getValue().toString().toLowerCase() == "true" ? SqlValue.TRUE : SqlValue.FALSE);
				break;
			default:
				if (groupID == 2) // Oracle的日期时间要转类型
				{
					if (cell.getCellstruct().getSqlType() == Types.TIMESTAMP) {
						where += columnName + "=to_timestamp('" + cell.getStringValue()
								+ "','yyyy-MM-dd HH24:MI:ss.ff')";
					} else {
						where += columnName + "=to_date('" + cell.getStringValue() + "','yyyy-mm-dd hh24:mi:ss')";
					}
				} else {

					where += columnName + "='" + cell.getValue() + "'";
				}
				break;
			}
		}
		return where;
	}
	
	@Override
	String getWhereIn(MCellStruct ms, ArrayList<String> items) {
		if (items == null || items.size() == 0) {
			return "1=2";
		}

		StringBuffer sb = new StringBuffer();
		sb.append(SqlFormat.keyword(ms.getColumnName(), DBType.ORACLE));
		sb.append(" In (");
		int groupID = DataType.getGroup(ms.getSqlType());
		String item;
		for (int i = 0; i < items.size(); i++) {
			item = items.get(i);
			if (groupID != 0) {
				item = StringUtils.strip(item, "\'");// 不是字母都尝试去掉分号
			}
			if (i > 999 && i % 1000 == 0) // oracle
																		// 列表中的最大表达数为1000
			{
				sb.delete(sb.length() - 1, sb.length());// 移除最后一个,号。
				sb.append(") or " + SqlFormat.keyword(ms.getColumnName(), DBType.ORACLE) + " In (");
			}
			if (StringUtils.isNotEmpty(item)) {
				if (groupID == 1) {
					sb.append(item + ",");
				} else {
					if (groupID == 2) {
						if (ms.getSqlType() == Types.TIMESTAMP) {
							sb.append("to_timestamp('" + item + "','yyyy-MM-dd HH24:MI:ss.ff'),");
						} else {
							sb.append("to_date('" + item + "','yyyy-mm-dd hh24:mi:ss'),");
						}
					} else {
						sb.append("'" + item + "',");
					}
				}
			}
		}
		return StringUtils.stripEnd(sb.toString(), ",") + ")";
	}

	@Override
	String formatWhereByColumn(String where, MDataColumn dcol) {
		return SqlFormat.FormatOracleDateTime(where, dcol);
	}
}
